package com.es.util.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;


public class Dao
{
	private DBBean dbb = null;
	private Connection conn = null;
	private PreparedStatement prepared = null;
	private ResultSet resultSet = null;
	private CallableStatement rs = null;
	private Statement statement = null;
	public Dao(){
		try {
			dbb = new DBBean();
			conn = dbb.getConn();
		} catch (Exception e) {
			conn = null;
			dbb = null;
		}
	}
  public ResultSet Select(String sql) throws Exception
  {
	  if(conn==null){
		  return null;
	  }
	  try {
	      this.prepared = conn.prepareStatement(sql);
	      this.resultSet = this.prepared.executeQuery();
	  } catch (SQLException ex) {
		  this.prepared = null;
		  throw ex;
	  }
	  return resultSet;
  }
  
  public ResultSet executePro(String sql) throws Exception
  {
	  if(conn==null){
		  return null;
	  }
	  try {
		  rs = conn.prepareCall(sql);
	      resultSet =  rs.executeQuery();
	  } catch (SQLException ex) {
		  this.prepared = null;
		  throw ex;
	  }
	  return resultSet;
  }
  
  @SuppressWarnings("unchecked")
public ResultSet executePro(String sql,ArrayList param) throws Exception
  {
	  if(conn==null){
		  return null;
	  }
	  try {
		  prepared = conn.prepareStatement(sql);
		  for (int i = 0; i < param.size(); ++i) {
		      setParameter(i + 1, param.get(i));
		    }
	      resultSet =  prepared.executeQuery();
	  } catch (SQLException ex) {
		  this.prepared = null;
		  throw ex;
	  }
	  return resultSet;
  }
  
  /**
   * sql规则：
   * 	1.以select开头，且不能到TOP
   * 	2.order by属性中只有一个desc，或ASC（asc是默认的 可以不写，同时建议只对一个字段进行排序）
   * 	如：
   * 		SELECT * from menu  ORDER BY menu_id  asc
   * 改变后的sql：
   * select * from (   select TOP 2 * FROM ( SELECT TOP 4 * from menu  ORDER BY menu_id  asc	) as t1	 ORDER BY menu_id DESC ) as t1   ORDER BY menu_id ASC;
   * @param sql
   * @param pageNav
   * @return
   * @throws Exception
   */
  @SuppressWarnings("unchecked")
public PageBean queryPageList(String sql,PageNavigator pageNav, ArrayList param) throws Exception
  {
	  PageBean pageBean = new PageBean();
	  
	  sql = sql.toLowerCase();
	  if(conn==null){
		  return null;
	  }
	  String[] order = DaoUtil.getOrder(sql);
	  try {
		  int currentPage = pageNav.getCurpage();
		  int pageRow = pageNav.getPageRow();
		  int totalPage = pageNav.getPageTotal();
		  int queryRow = 0;
		  if(currentPage==totalPage){
			  queryRow= pageNav.getRowTotal() - (totalPage-1)*pageRow;
		  }
		  else queryRow = pageRow;
		  pageNav.setCurRow(queryRow);
		  String querySql = "select * from (   select TOP "+queryRow+" * FROM ( SELECT TOP "
				  		+currentPage*pageRow
				  		+" "
				  		+order[0]+order[1]+order[2]
				  		+" ) as t1 "
				  		+order[1]+order[3]+ " ) as t1 "
				  		+order[1]+order[2];
		  
		  this.prepared = conn.prepareStatement(querySql);
	      for (int i = 0; i < param.size(); ++i) {
		      setParameter(i + 1, param.get(i));
		    }
	      this.resultSet = this.prepared.executeQuery();
	  } catch (SQLException ex) {
		  this.prepared = null;
		  throw ex;
	  }
	  ResultSetMetaData rss = resultSet.getMetaData();    
	  ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
	  while(resultSet.next()){
		  HashMap map = new HashMap();
		  for(int i = 0;i<rss.getColumnCount(); i++){ 			  
			  map.put(rss.getColumnName(i+1), resultSet.getObject(rss.getColumnName(i+1)));    
		 }	
		  list.add(map);
	  }
	  pageBean.setPage(pageNav);
	  pageBean.setParam(param);
	  pageBean.setMap(list);
	  return pageBean;
  }

  public int getAllRow(String sql,ArrayList param) throws Exception
  {
	  int rowCount = 0;
	  if(conn==null){
		  return 0;
	  }
	  sql = "select COUNT(*) num FROM ("+sql+")T";
	  try {
		  this.prepared = conn.prepareStatement(sql);
	      for (int i = 0; i < param.size(); ++i) {
		      setParameter(i + 1, param.get(i));
		  }
	      this.resultSet = this.prepared.executeQuery();
	  } catch (SQLException ex) {
		  this.prepared = null;
		  throw ex;
	  }
	  if(resultSet.next()){
		  rowCount = resultSet.getInt("num");
	  };
	  return rowCount;
  }
  
  public ResultSet Select(String sql, ArrayList param) throws Exception
  {
	  	
	  if(conn==null){
		  return null;
	  }
	  try
	   {
	      this.prepared = conn.prepareStatement(sql);
	      for (int i = 0; i < param.size(); ++i) {
		      setParameter(i + 1, param.get(i));
		    }
	      this.resultSet = this.prepared.executeQuery();
	    } catch (SQLException ex) {
	      this.prepared = null;
	      throw ex;
	    }
	    return this.resultSet;
  }

  @SuppressWarnings("unchecked")
public void update(String sql, ArrayList param)
    throws Exception
  {
    int count = 0;
    if(conn==null){
		  return ;
	}
    try {
      this.prepared = conn.prepareStatement(sql);
      if (param != null)
      {
        for (int i = 0; i < param.size(); ++i) {
          setParameter(i + 1, param.get(i));
        }
      }
      
      count = this.prepared.executeUpdate();
    }
    catch (Exception ex)
    {
      this.prepared = null;
      throw ex;
    }
    if (count >= 0)
      return;
    throw new Exception("update fail!");
  }
  

  @SuppressWarnings("unchecked")
public void updateBatch(String sql, ArrayList<ArrayList> param)throws Exception{
      if(conn==null){
  		  return ;
  	}
      try {
        prepared = conn.prepareStatement(sql);
        conn.setAutoCommit(false);   
        prepared.clearBatch();   

        if (param != null)
        {
          for (int j = 0; j < param.size(); ++j) {
        	  for (int i = 0; i < param.get(j).size(); ++i) {
                  setParameter(i + 1, param.get(j).get(i));
                }
            prepared.addBatch(); 
          }
        }
        prepared.executeBatch();
        conn.commit();  
      }
      catch (Exception ex)
      {
    	conn.rollback();  
        prepared = null;
        throw ex;
      }
    }
  
  @SuppressWarnings("unchecked")
  public void updateBatchBySql( String[] param)throws Exception{
      if(conn==null){
  		  return ;
  	}
      try {
    	  statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        conn.setAutoCommit(false);   
        statement.clearBatch();   

        if (param != null)
        {
          for (int i = 0; i < param.length; ++i) {
        	  statement.addBatch(param[i]);
          }
        }
        prepared.executeBatch();
        conn.commit();  
      }
      catch (Exception ex)
      {
    	conn.rollback();  
        prepared = null;
        throw ex;
      }
    }
  
  public void commit() throws Exception{
	  this.conn.commit();
  }
  
  public void rollback() throws Exception{
	  this.conn.rollback();
  }

  private void setParameter(int index, Object param) throws Exception {
    try {
      if(param==null){
    	  prepared.setString(index, null);
    	  return;
      }
      if (param instanceof String)
        prepared.setString(index, (String)param);
      if (param instanceof Character)
        prepared.setString(index, ((Character)param).toString());
      if (param instanceof Integer)
        prepared.setInt(index, ((Integer)param).intValue());
      if (param instanceof Double)
        prepared.setDouble(index, ((Double)param).doubleValue());
      if (param instanceof Date)
        prepared.setDate(index, (Date)param);
    } catch (SQLException ex) {
      throw ex;
    }
  }

  public void close()
  {
    if (this.resultSet != null)
    {
      try
      {
        this.resultSet.close();
        this.resultSet = null;
      }
      catch (Exception ex)
      {
        ex.printStackTrace();
      }
    }
    if (this.prepared != null)
    {
      try
      {
        this.prepared.close();
        this.prepared = null;
      }
      catch (Exception ex)
      {
        ex.printStackTrace();
      }
    }
    if (this.statement != null){
	    try
	    {
	      this.statement.close();
	      this.statement = null;
	    }
	    catch (Exception ex)
	    {
	      ex.printStackTrace();
	    }
    }
    if (this.rs != null){
	    try
	    {
	      this.rs.close();
	      this.rs = null;
	    }
	    catch (Exception ex)
	    {
	      ex.printStackTrace();
	    }
    }
    if (this.conn != null){
	    try
	    {
	      this.conn.close();
	      this.conn = null;
	    }
	    catch (Exception ex)
	    {
	      ex.printStackTrace();
	    }
    }
  }
}